#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Author:    御风
# @Mail:      zhong.yufeng@foxmail.com
# @Time:      2022/4/12 0:25


import pandas as pd
from sqlalchemy import Column, Integer, String, Date, DateTime
from utils.db.module import Base
from utils.db.database import DataBase


class CategoryItem(Base):
    """
    CREATE TABLE `sys_category` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `category_name` varchar(255) NOT NULL,
        `create_time` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
        `create_peo` varchar(255) DEFAULT NULL,
        `update_time` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
        `update_peo` varchar(255) DEFAULT NULL,
        `remark` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    """

    __tablename__ = "sys_category"

    id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
    category_name = Column(String(255), nullable=False)
    create_time = Column(DateTime, nullable=True)
    create_peo = Column(String(255), nullable=True)
    update_time = Column(DateTime, nullable=True)
    update_peo = Column(String(255), nullable=True)
    remark = Column(String(255), nullable=True)


class CategoryDataBase(DataBase):

    def __init__(self):
        super(CategoryDataBase, self).__init__()
        self.table = CategoryItem.__tablename__

    @property
    def categorys(self) -> list:
        sql = f"SELECT `category_name` FROM `{self.table}`;"
        df = pd.read_sql(sql, self.engine)
        return df["category_name"].values.tolist()

    @property
    def categorysDetails(self) -> list:
        sql = f"SELECT `id`, `category_name` FROM `{self.table}`;"
        df = pd.read_sql(sql, self.engine)
        result = []
        for tup in zip(df["id"], df["category_name"]):
            result.append({
                "id":   tup[0],
                "name": tup[1],
            })
        return result


if __name__ == "__main__":
    import json

    db = CategoryDataBase()

    print(json.dumps(db.categorys, ensure_ascii=False, separators=(", ", ": ")))
    """
    ["主食", "零食", "母婴用品", "女性用品", "酒水饮料", "日用品", "团购平台"]
    """

    print(json.dumps(db.categorysDetails, ensure_ascii=False, separators=(", ", ": ")))
    """
    [{
        "id":   1,
        "name": "主食"
    }, {
        "id":   2,
        "name": "零食"
    }, {
        "id":   3,
        "name": "母婴用品"
    }, {
        "id":   4,
        "name": "女性用品"
    }, {
        "id":   5,
        "name": "酒水饮料"
    }, {
        "id":   6,
        "name": "日用品"
    }, {
        "id":   7,
        "name": "团购平台"
    }]
    """
